<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); 

class Project165_helper {

    function some_function(){	
		$CI =& get_instance();
		$CI->load->database();
		echo "HI THIS IS A LIBRARY FUNCTION";
    }

	function get_fname($studnum){
		$CI =& get_instance();
		$CI->load->database();
		$sql_fname = "SELECT firstname
						FROM students
						WHERE studentno = '$studnum'";
		$query_fname = $CI->db->query($sql_fname);
		$fname = $query_fname->row()->firstname;
		return $fname;
	}
	
	function get_lname($studnum){
		$CI =& get_instance();
		$CI->load->database();
		$sql_lname = "SELECT lastname
						FROM students
						WHERE studentno = '$studnum'";
		$query_lname = $CI->db->query($sql_lname);
		$lname = $query_lname->row()->lastname;
		return $lname;
	}	
	
	function get_coursename($studnum){
		$CI =& get_instance();
		$CI->load->database();
		
		$sql_coursename = "SELECT coursename FROM courses INNER JOIN
							students ON courses.courseid = students.courseid
							WHERE studentno = '$studnum'";
		$query_coursename = $CI->db->query($sql_coursename);
		$coursename = $query_coursename->row()->coursename;
		return $coursename;
	}
	
	function get_year_standing($studentno){
		$CI =& get_instance();
		$CI->load->database();	
		$sql_standing ="SELECT sum(units)
						FROM subjectstaken
						INNER JOIN subjects
						ON subjects.subjectid = subjectstaken.subjectid
						WHERE grade <= 3.0
						AND studentno ='$studentno'";
		$query_standing = $CI->db->query($sql_standing);
		if ($query_standing->num_rows == 0){
			$units_taken = 0;
		}
		else{
			$units_taken = $query_standing->row()->sum;
		}
		
		if ($units_taken <= 23){
			return 1;
		}
		else if ($units_taken >=24 AND $units_taken <= 47){
			return 2;
		}
		else if ($units_taken >= 48 AND $units_taken <= 71){
			return 3;
		}
		else if ($units_taken >= 72 AND $units_taken <= 95){
			return 4;
		}
		else if ($units_taken >= 96){
			return 5;
		}
	}
	
	function is_substitute_and_in_cur($subject,$studentno){
		$CI =& get_instance();
		$CI->load->database();	
		$sql_help = "SELECT subjectid FROM subjects
					WHERE subjectname ='$subject'";
		$query_help = $CI->db->query($sql_help);
		$subjectid =$query_help->row()->subjectid;
		
					
		$sql_sub = "SELECT  subjectid,replacement FROM substitutes
					WHERE replacement =$subjectid";
		$query_sub = $CI->db->query($sql_sub);
		if ($query_sub->num_rows == 0){
			return false;
		}
		else{
			$pinagmulan = $query_sub->row()->subjectid;
			$sql_help2 = "SELECT subjectname FROM subjects WHERE subjectid =$pinagmulan";
			$query_help2 = $CI->db->query($sql_help2);
			$subjectname = $query_help2->row()->subjectname;
			if ($CI->project165_helper->in_curriculum($subjectname,$studentno)){
				return true;
			}
			else{
				return false;
			}
		}	
	}
	
	function year_level_satisfied($subjectname,$studentno){
		$CI =& get_instance();
		$subjects = array('PI 100');
		if (in_array($subjectname,$subjects)){
			$year_standing = $CI->project165_helper->get_year_standing($studentno);
			if ($year_standing >= 3){
				return 1;
			}
			else{
				return 0;
			}
		}
		else{
			return 1;
		}
	}	
	function is_major_elective($subjectname){
		$CI =& get_instance();
		$CI->load->database();	
	
		$sql_m_e = "SELECT subjectid 
					FROM subjects
					WHERE subjectname ='$subjectname' 
					AND label = 'Major/Elective'";
					
		$query_m_e = $CI->db->query($sql_m_e);
		if ($query_m_e->num_rows == 0){
			return false;
		}
		else{
			//echo 'hi';
			return true;
		}
	}
	
	function find_prereq($subject){ //returns an array of prerequisites. Null if no prerequisites
		$CI =& get_instance();
		$CI->load->database();
		
		$sql_subjectid = "SELECT subjectid FROM subjects WHERE subjectname = '{$subject}'";
		$query_subjectid = $CI->db->query($sql_subjectid); //get the subjectid
		$subjectid = $query_subjectid->row()->subjectid;
		
		
		$sql_prereq = "SELECT prereq FROm prerequisites
				WHERE subjectid = '$subjectid'";
		$query_prereq = $CI->db->query($sql_prereq); //the prerequisites
		$result = null;

		return $query_prereq; 
	}
	
	function find_substitute($subjectid){//returns the array of subsitute for a subject
		$CI =& get_instance();
		$CI->load->database();	
		$sql_substitute = "SELECT * FROM substitutes
							WHERE subjectid = $subjectid";
		$query_substitute = $CI->db->query($sql_substitute);
		return $query_substitute;
	}
	
	function is_taken_and_passing($subjectid,$studentno){
		$CI =& get_instance();
		$CI->load->database();	
		$sql_taken = "SELECT subjectid
					FROM subjectstaken
					WHERE studentno = '$studentno'
					AND grade <= 3.0
					AND subjectid ='$subjectid'";
		$query_taken = $CI->db->query($sql_taken);
		if ($query_taken->num_rows == 0){
			return false;
		}
		else {
			return true;
		}
	}
	function get_subjects_enlisted($studentno){
		$CI =& get_instance();
		$CI->load->database();	

		$sql_enlisted = "SELECT subjects.description,subjects.subjectid,subjectname,units FROM subjectsenlisted 
						INNER JOIN subjects ON subjects.subjectid = subjectsenlisted.subjectid 
						WHERE studentno = '{$studentno}'"; //get all enlisted subjects of a student
		$query_enlisted = $CI->db->query($sql_enlisted);
		return $query_enlisted;
	}
	function get_subjects_taken($studentno){
		$CI =& get_instance();
		$CI->load->database();

		$sql_taken = 	"SELECT subjects.subjectid,grade,subjectname,units FROM subjectstaken
						INNER JOIN subjects ON subjects.subjectid =subjectstaken.subjectid
						WHERE studentno = '$studentno'
						AND subjectstaken.grade <= 3.0"; //INC
		$query_taken = $CI->db->query($sql_taken);
		return $query_taken;
	}
	
	function is_enlisted($subjectname,$studentno){
		$CI =& get_instance();
		$CI->load->database();	
		$sql_get_id = "SELECT subjectid
						FROM subjects
						WHERE subjectname='$subjectname'";
		$query_get_id = $CI->db->query($sql_get_id);
		$subjectid = $query_get_id->row()->subjectid;

		$sql_enlisted = "SELECT subjectid
							FROM subjectsenlisted
							WHERE subjectid = $subjectid 
							AND studentno ='$studentno'";
		$query_enlisted = $CI->db->query($sql_enlisted);
		if ($query_enlisted->num_rows() > 0){
			return true;
		}
		else{
				return false;
		}
		
	}
	
	function enlist_subject($subjectname,$studentno){
		/*
			This function enlists the student on a particular subject
		*/
		$CI =& get_instance();
		$CI->load->database();	
		$sql_get_id = "SELECT subjectid
						FROM subjects
						WHERE subjectname='$subjectname'";
		$query_get_id = $CI->db->query($sql_get_id);
		$subjectid = $query_get_id->row()->subjectid;
		
		$sql_enlist = "INSERT INTO subjectsenlisted 
						(subjectid,studentno) 
						VALUES($subjectid,'$studentno')";
		$query_enlist = $CI->db->query($sql_enlist);
	}

	
	
	function submit_grade($subjectname,$studentno,$grade){
		/*
			This function gives the student a grade;
		*/
		$CI =& get_instance();
		$CI->load->database();

		$sem;
		$academic_year_start = date('Y'); //get current year
		//$academic_year_end = $academic_year_start+1;
		$current_month = date('m'); //get current date
		
		$first_sem = array("6","7","8","9","10");
		$second_sem = array("11","12","1","2","3");
		//$summer = array("4","5");
		
		if (in_array($current_month,$first_sem)){
			$sem = 1;
		}
		else if (in_array($current_month,$second_sem)){
			$academic_year_start = (int)$academic_year_start-1;
			$sem = 2;	
		}
		else{
			$sem = 3;
		}
		
		$sql_get_id = "SELECT subjectid
						FROM subjects
						WHERE subjectname='$subjectname'";
		$query_get_id = $CI->db->query($sql_get_id);
		$subjectid = $query_get_id->row()->subjectid;			

		$sql_submit = "INSERT INTO subjectstaken
						(studentno,subjectid,grade,acadyear_start,semtaken)
						VALUES('$studentno',$subjectid,$grade,$academic_year_start,$sem)";
		$query_submit = $CI->db->query($sql_submit);
		
		$sql = "DELETE from subjectsenlisted
				WHERE subjectid = $subjectid";
		$query = $CI->db->query($sql);
		$_SESSION['subjects_left'] = $CI->project165_helper->get_subjects_enlisted($studentno);
	}
	function get_label($subjectname){
		$CI =& get_instance();
		$CI->load->database();
		$sql_label = "SELECT label
					FROM subjects
					WHERE subjectname ='$subjectname'";
		$query_label = $CI->db->query($sql_label);
		return $query_label;
	}
	function get_curriculum($studentno){
		$CI =& get_instance();
		$CI->load->database();
		$sql_help = "SELECT courseid 
					FROM students
					WHERE studentno = '$studentno'";
		$query_help = $CI->db->query($sql_help);
		$courseid = $query_help->row()->courseid;
		
		$sql_curriculum = "SELECT curriculums.subjectid, subjectname, description, units
							FROM curriculums 
							INNER JOIN subjects
							ON subjects.subjectid = curriculums.subjectid
							WHERE courseid = $courseid
							ORDER BY year,sem";
		$query_curriculum = $CI->db->query($sql_curriculum);
		return $query_curriculum;			
	}
	function get_curriculum_specified($studentno, $year, $sem){
		$CI =& get_instance();
		$CI->load->database();
		
		$sql_peryear = "SELECT subjectname, description, units FROM subjects
							INNER JOIN curriculums ON subjects.subjectid = curriculums.subjectid
							AND year = $year AND sem = $sem
							INNER JOIN students ON students.courseid = curriculums.courseid
							AND studentno = '$studentno'";
		$query_peryear = $CI->db->query($sql_peryear);
		return $query_peryear;
	}
	function in_curriculum($subjectname,$studentno){//subjectid,couseid
		$CI = & get_instance();
		$CI->load->database();
		$sql_id = "SELECT subjectid FROM subjects WHERE subjectname='$subjectname'"; // get subjectid
		$query_id = $CI->db->query($sql_id);
		$subjectid = $query_id->row()->subjectid;
		
		$sql_course = "SELECT courseid FROM students WHERE studentno = '$studentno'";
		$query_course = $CI->db->query($sql_course);
		$courseid = $query_course->row()->courseid;
		
		$sql_cur = "SELECT subjectid FROM curriculums WHERE subjectid ='$subjectid' AND courseid = '$courseid'";
		$query_cur = $CI->db->query($sql_cur);
		if ($query_cur->num_rows() == 0){
			return false;
		}
		else{
			return true;
		}
	}
	function is_major($subjectname){
		$CI = & get_instance();
		$CI->load->database();
		$sql_major = "SELECT label
						FROM subjects
						WHERE subjectname ='$subjectname'";
		$query_major = $CI->db->query($sql_major);
		if ($query_major->row()->label == 'Major'){
			return true;
		}
		else{
			return false;
		}		
		
	}
	function exceeded($subjectname,$studentno){
		$CI =& get_instance();
		$CI->load->database();
	
		$sql_courseid = "SELECT courseid 
						FROM students 
						WHERE studentno ='$studentno'";
		$query_courseid = $CI->db->query($sql_courseid);
		$courseid = $query_courseid->row()->courseid;
		
		$sql_label = "SELECT label
						FROM subjects
						WHERE subjectname = '$subjectname'";
		$query_label = $CI->db->query($sql_label);
		$label = $query_label->row()->label; // AH? SSP?

		if ($label == 'AH/PhilStud' OR $label =='AH' OR $label =='English/AH'){
			//get required AH, look for taken AH
			$result = $CI->project165_helper->get_required_others($courseid,'AH');
			$required_AH = $result->row()->ah; //AH required
			
			$taken_AH = $CI->project165_helper->get_taken_others($studentno,'AH');
			if ($taken_AH < $required_AH){
				
				return false;
			}
			else{
				return true;
			}
		}
		else if ($label == 'SSP/PhilStud' OR $label =='SSP'){
			$result = $CI->project165_helper->get_required_others($courseid,'SSP');
			$required_SSP = $result->row()->ssp; //SSP required
			
			$taken_SSP = $CI->project165_helper->get_taken_others($studentno,'SSP');
			if ($taken_SSP < $required_SSP){
				return false;
			}
			else{
				return true;
			}		
		}
		else if ($label == 'MST'){
			$result = $CI->project165_helper->get_required_others($courseid,$label);
			$required_MST = $result->row()->mst; //MST required
			
			$taken_MST = $CI->project165_helper->get_taken_others($studentno,$label);
			if ($taken_MST < $required_MST){
				return false;
			}
			else{
				return true;
			}		
		}
	}
	function get_taken_others($studentno,$label){
		$CI=& get_instance();
		$CI->load->database();
		
		$sql_taken = "SELECT SUM(units)
						FROM subjects
						INNER JOIN subjectstaken
						ON subjects.subjectid  = subjectstaken.subjectid
						WHERE label like '%$label%'
						AND studentno ='$studentno'
						AND grade <= 3.0";
		$query_taken = $CI->db->query($sql_taken);
		return $query_taken->row()->sum;
	}
	function get_required_others($courseid,$label){
		$CI =& get_instance();
		$CI->load->database();
		$sql_required = "SELECT $label 
						FROM courses
						WHERE courseid = $courseid";
		$query_required = $CI->db->query($sql_required);
		return $query_required;					
	}
	function get_subjects_to_take($studentno){
		$CI =& get_instance();
		$CI->load->database();	
		$sql_courseid = "SELECT courseid
						FROM students
						WHERE studentno ='$studentno'";
		$query_courseid = $CI->db->query($sql_courseid);
		$courseid = $query_courseid->row()->courseid;
		
		$sql_not_taken = "SELECT description,subjectname,curriculums.subjectid,units 
							FROM curriculums
							INNER JOIN subjects
							ON subjects.subjectid = curriculums.subjectid
							WHERE curriculums.subjectid 
							NOT IN(SELECT subjectid 
							FROM subjectstaken 
							WHERE studentno = '$studentno' 
							AND grade <= 3) 
							AND courseid = $courseid";					
		$query_not_taken = $CI->db->query($sql_not_taken);
		return $query_not_taken;
	}
	function enlist_me($subject,$studentno){
			$CI =& get_instance();
			$prereq = $CI->project165_helper->find_prereq($subject);
			if ($prereq->num_rows == 0){ //no prereqs
				return 1;
			}
			else{
				//check if prereq is already taken
				//if prereq is not taken, check if there is a substitute
				$taken; //
				$i = 1;
				$k = 1;
				$substitutes;
				foreach($prereq->result() AS $to_take){
						//echo "looking for prereqs";
						if($CI->project165_helper->is_taken_and_passing($to_take->prereq,$studentno)){
							$taken[$i] = 1;
							//echo "passed";
						}
						else{
							//echo "failed";
							$taken[$i] = 0;
						}
						$i++;
					}
				//$taken tells what subjects are not taken
				if (!isset($taken)){
					
				}
				else if (in_array(0,$taken)){ //a prereq is not yet taken
					//echo "check for substitutes";
					for($j = 1; $j<$i; $j++){
						if ($taken[$j] == 0){
							$subs = $CI->project165_helper->find_substitute($prereq->row($j)->prereq);//get the substitutes
							//echo "looking for substitutes";
							foreach($subs->result() AS $test){//iterate thru all the substitute and check if they are taken
								$condition = $test->condition; //to replace
								if($CI->project165_helper->is_taken_and_passing($test->replacement,$studentno)){
									$substitutes[$k] = 1;
								}
								else{
									$substitutes[$k] = 0;
								}
								$k++;
							}
							if (!isset($condition)){
							}
							else if ($condition == 0){ //or
								if (in_array(1,$substitutes)){
									$taken[$j] = 1;
								}
								else{
									$taken[$j] = 0;
								}
							}
							else if ($condition == 1){//and
								if (in_array(0,$substitutes)){
									$taken[$j] = 0;
								}
								else{
									$taken[$j] = 1;
								}
							}
						}
						else{//a prereq is taken
						}
					}
					if(in_array(0,$taken)){
						//echo "can't enlist";
						return 0;
					}
					else{
						//echo "can enlist all prereqs satisfied";
						return 1;
					}
				}
				else{ //all prereqs are taken 
					//enlist now!
					return 1;
				}		
		}
	}
	function get_subjects_taken_all($studentno,$acadyear_start,$sem){
		$CI =& get_instance();
		$CI->load->database();		
	
		$sql_all = "SELECT DISTINCT subjects.subjectid,subjectname,units,grade,acadyear_start FROM subjectstaken
					INNER JOIN subjects
					ON subjects.subjectid = subjectstaken.subjectid
					WHERE studentno ='$studentno'
					AND acadyear_start = '$acadyear_start'
					AND semtaken = $sem";
		$query_all = $CI->db->query($sql_all);
		return $query_all;
	}
	function mark_taken($array, $studnum){
		$CI =& get_instance();
		$CI->load->library('Project165_helper');
		$marker;
		$i = 0;
		foreach($array->result() AS $test){
			$has_sub = $CI->project165_helper->find_substitute($test->subjectid);
			if ($has_sub->num_rows == 0){
				$marker[$i] = false;
			}
			else{
				$cond = $has_sub->row(1)->condition;
				foreach($has_sub->result() AS $sub){
					$subjectid = $sub->replacement;
					if ($cond == 0){ //or
						if ($CI->project165_helper->is_taken_and_passing($subjectid,$studnum) == true){
							$marker[$i] = true;
							break;
						}
						else{
							$marker[$i] = false;
						}
					}
					else{
						if ($CI->project165_helper->is_taken_and_passing($subjectid,$studnum) == true){
							$marker[$i] = true;
						}
						else{
							$marker[$i] = false;
							break;
						}
					}
				}
			}
			$i++;
		}
		return $marker;
	}
	
	function summary($studentno,$label){
		$CI =& get_instance();
		$CI->load->database();
		
		$sql_course = "SELECT courseid FROM students WHERE studentno = '$studentno'";
		$query_course = $CI->db->query($sql_course);
		$courseid = $query_course->row()->courseid;
		
		$sql_taken_units = "SELECT MAX($label) AS Required, CASE WHEN Max($label) IS NULL
								THEN (Select $label FROM Courses WHERE CourseID = $courseid) END,
								SUM(Units) AS Credited, '$label' AS Category
								FROM Courses, SubjectsTaken INNER JOIN Subjects ON
								SubjectsTaken.SubjectID = Subjects.SubjectID
								WHERE StudentNo = '$studentno' AND CourseID = $courseid
								AND Grade <= 3.0 AND Label LIKE '%$label%'";
							
		$query_taken_units = $CI->db->query($sql_taken_units);
		return $query_taken_units;
	}

	function compute_gwa($studentno){
		$CI =& get_instance();
		$grades = $CI->project165_helper->get_subjects_taken($studentno);
		$gwa = 0;
		$sum_units = 0;
		if ($grades->num_rows == 0){
			return $gwa;
		}
		else{
			foreach ($grades->result() as $temp){
				$sum_units = $sum_units + $temp->units;
				$gwa =  $gwa+$temp->units * $temp->grade;
				
			}
			$gwa = $gwa/$sum_units;
		}
		return $gwa;
	}
	
	function trial(){
		$CI =& get_instance();
		$CI->project165_helper->some_function();
	}
}

?>